/**
 * @(#)Database.java        1.0 2/26/12
 * 
 * Copyright (c) 2012 public static droid main
 * Georgia Institute of Technology, Atlanta, GA
 * All rights reserved.
 */

package edu.gatech.oad.publicstaticdroidmain.taskmanager.model;

import java.util.Date;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * An abstraction of an Android SQLite database used to store user
 * data.
 * 
 * @version 1.0 2/26/12
 * @author Daniel Hull
 *
 */
public class Database implements DatabaseInterface {
	
	private SQLiteDatabase database;
	private DBHelper DBhelper;
	private final Context context;
	
	/**
	 * Instantiates a new database.  You must call open() on this 
	 * database before it can be used to retrieve or store data.
	 *
	 * @param c the Context in which the Database will be used
	 */
	public Database(Context c) {
		context = c;
	}
	
	/**
	 * Connects this database to the Android SQLite backend.  Must
	 * be called before any other databse method can be used.
	 *
	 * @return this object
	 */
	public Database open() {
		DBhelper = new DBHelper(context);
		database = DBhelper.getWritableDatabase();
		return this;
	}
	
	/**
	 * Closes the connection between this database and the Android
	 * SQLite backend.  Must be called when the database is no longer
	 * needed.  You must call open() on this Database if you want to
	 * reuse it.
	 */
	public void close() {
		DBhelper.close();
	}
	
	
	/**
	 * Returns a UserAccount containing all of the specified user's data
	 * (including Tasks).
	 *
	 * @param username the username
	 * @param password the password
	 * @return a UserAccount object containing all of the specified user's
	 * 		data; null if the specified user does not exist in
	 * 		the database
	 */
	public UserAccount selectUserData(String username, String password) {
		
		Cursor c1 = database.rawQuery("SELECT Category " +
									  "FROM Type " +
									  "WHERE Username='" + username + "'"
									  , null);
		
		
		int NAME=0, EMAIL=1, TNAME=2, TDESCRIPTION=3, TDUEDATE=4, TLAT=5, TLON=6, TRECURRENCE=7, TCATEGORY=8, ID = 9, COMPLETE = 10;
		
		Cursor c2 = database.rawQuery("SELECT User.Name, User.Email, Tasks.Tname, Tasks.Tdescription, " +
									 		 "Tasks.TdueDate, Tasks.Tlat, Tasks.Tlon, Tasks.Trecurrence, " +
									 		 "Tasks.Tcategory, Tasks.ID, Tasks.Tcomplete " +
									 "FROM User LEFT JOIN Tasks ON User.Username=Tasks.Username " +
									 "WHERE User.Username='" + username + "'"
									, null);
		c2.moveToFirst();
		UserAccount account = new UserAccount(username, password, c2.getString(NAME), c2.getString(EMAIL));
		
		for(c1.moveToFirst(); !c1.isAfterLast(); c1.moveToNext()) {
			account.addCategory(c1.getString(0));
		}
		
		
		if (!c2.isNull(TNAME)) {	
			Task t;
			for(c2.moveToFirst(); !c2.isAfterLast(); c2.moveToNext()) {
			    	account.addCategory(c2.getString(TCATEGORY));
			    	t = new Task(c2.getString(TNAME), c2.getString(TDESCRIPTION), new Date(c2.getLong(TDUEDATE)), c2.getDouble(TLAT), 
	    								c2.getDouble(TLON), Task.Recurrence.valueOf(c2.getString(TRECURRENCE)));
			    	t.setId(c2.getLong(ID));
			    	t.setCompleted((c2.getInt(COMPLETE)==1)?true:false);
			    	account.addTask(t, c2.getString(TCATEGORY));
			}
			
		}
		
		return account;
	}
	/**
	 * Inserts a task into the Database under a user's information
	 * @param t the task
	 * @param username the username of the owner of the task
	 * @param category the category of the task
	 */
	public void insertTask(Task t, String username, String category) {
		database.execSQL("INSERT INTO Tasks " +
							"VALUES ('" + 	t.getId() +           		"','" +
											username +           		"','" +
											t.getName() +        		"','" + 
											t.getDescription() + 		"', " +
											t.getDueDate().getTime() +  " , " +
											t.getLatitude() +    		" , " +
											t.getLongitude() +   		" ,'" +
											t.getRecurrence() +  		"','" +
											category +			"'," +
											((t.isCompleted()==true)?1:0) + ")"
				);
	}
	/**
	 * Deletes a task from the database
	 * @param ID the ID number of the task
	 * @return returns false...
	 */
	public void deleteTask(long ID) {
		database.execSQL("DELETE FROM Tasks WHERE ID=" + ID);
	}
	
	/**
	 * Updates a task in the database
	 * @param t the task with updated information
	 * @param username the username of the owner of the task
	 * @return false...
	 */
	public boolean updateTask(Task t, String username) {
		//TODO
		database.execSQL("UPDATE Tasks" +
								"SET Tname=" + t.getName() + 
									"Tdescription=" + t.getDescription() + 
									"TdueDate=" + t.getDueDate() + 
									"Tlat=" + t.getLatitude() + 
									"Tlon=" + t.getLongitude() + 
									"Trecurrence=" + t.getRecurrence() + 
								"WHERE Tusername=" + username
								);
		return false;
	}
	
	/**
	 * creates a user based on the input strings on the CreateAccountActivity page
	 * @param username the new username
	 * @param password the password for new username
	 * @param emailAddress the user's email
	 * @param realName the user's real name
	 */
	public void createUser(String username, String password, String emailAddress, String realName) {
		
		database.execSQL("INSERT INTO User " +
						 "VALUES ('" + realName + "', '" + 
							  		   emailAddress + "', '" +
									   username + "', '" +
									   password + "')"
						);
		database.execSQL("INSERT INTO Type VALUES ('" + username + "', 'Personal')");
		database.execSQL("INSERT INTO Type VALUES ('" + username + "', 'School')");
		database.execSQL("INSERT INTO Type VALUES ('" + username + "', 'Work')");
	}
	
	/**
	 * tests if the login information is a valid username password comination
	 * @param username the username
	 * @param password the associated password
	 * @return true if valid, false if invalid
	 */
	public boolean validLogIn(String username, String password) {
		int count;
		Cursor c = database.rawQuery("SELECT count(*) FROM User WHERE Username='" + username + "' AND Password='" + password + "'", null);
		c.moveToFirst();
		count = c.getInt(0);
		return count>0;
	}
	/**
	 * Tests if the username is valid
	 * @param username the username
	 * @return true if valid false if invalid
	 */
	public boolean validUsername(String username) {
		int count;
		Cursor c = database.rawQuery("SELECT count(*) FROM User WHERE Username='" + username + "'", null);
		c.moveToFirst();
		count = c.getInt(0);
		return count<=0;
	}
	
	private static final String DATABASE_NAME = "TaskManager";
	private static final int DATABASE_VERSION = 1;
	
	private class DBHelper extends SQLiteOpenHelper {

		public DBHelper(Context c) {
			super(c, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(	"CREATE TABLE User (" + 
							"Name TEXT NOT NULL, " +
							"Email TEXT NOT NULL, " +
							"Username TEXT NOT NULL PRIMARY KEY, " +
							"Password TEXT NOT NULL" +
							")");
			db.execSQL(	"CREATE TABLE Tasks (" +
							"ID LONG PRIMARY KEY, " +
							"Username TEXT NOT NULL, " +
							"Tname TEXT NOT NULL, " +
							"Tdescription TEXT NOT NULL, " +
							"TdueDate LONG, " +
							"Tlat DOUBLE PRECISION, " +
							"Tlon DOUBLE PRECISION, " +
							"Trecurrence TEXT, " + 
							"Tcategory TEXT," +
							"Tcomplete INTEGER" +
							")");
			db.execSQL(	"CREATE TABLE Type (" +
							"Username TEXT NOT NULL, " +
							"Category TEXT NOT NULL" +
							")");
			db.execSQL("INSERT INTO User VALUES ('default', 'default', 'default', 'default')");
			db.execSQL("INSERT INTO Type VALUES ('default', 'Personal')");
			db.execSQL("INSERT INTO Type VALUES ('default', 'School')");
			db.execSQL("INSERT INTO Type VALUES ('default', 'Work')");
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			db.execSQL("DROP TABLE IF EXISTS User");
		}
		
	}

}
